﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration


Public Class ProviderSqlServer

    Implements IDbProvider

    Private Property cnn As SqlConnection
    Friend Property ConnectionString As String Implements IDbProvider.ConnectionString

    Friend Function DataReader() As IDataReader Implements IDbProvider.DataReader
        Throw New NotImplementedException
        Return Nothing
    End Function

    Friend Function Parameter() As IDataParameter Implements IDbProvider.Parameter
        Return New SqlParameter
    End Function

    Friend Function StoredProc(ByVal spName As String, ByVal UserId As Integer) As Integer Implements IDbProvider.StoredProc
        Return StoredProc(spName, Nothing, Nothing, UserId)
    End Function

    Friend Function StoredProc(ByVal spName As String, ByVal Params As List(Of IDataParameter), ByVal UserId As Integer) As Integer Implements IDbProvider.StoredProc
        Return StoredProc(spName, Params, Nothing, UserId)
    End Function

    Friend Function StoredProc(ByVal spName As String, ByVal oReturn As DataTable, ByVal UserId As Integer) As Integer Implements IDbProvider.StoredProc
        Return ExecStoredProc(spName, Nothing, oReturn, UserId)
    End Function

    Friend Function StoredProc(ByVal spName As String, ByVal Params As List(Of IDataParameter), ByVal oReturn As DataTable, ByVal UserId As Integer) As Integer Implements IDbProvider.StoredProc
        Return ExecStoredProc(spName, Params, oReturn, UserId)
    End Function

    Friend Function ExecStoredProc(ByVal spName As String, ByVal Params As List(Of IDataParameter), ByVal oReturn As System.Data.DataTable, ByVal UserId As Integer) As Integer
        Dim lRet As Long
        Dim bAddUserParameter As Boolean
        Using cnn
            cnn = New SqlConnection(ConnectionString)
            cnn.Open()
            Using cmd As New SqlCommand
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Connection = cnn
                cmd.CommandText = spName

                If Not Params Is Nothing Then
                    AddParamsToCmd(Params, cmd)
                    bAddUserParameter = FindParameter(Params, "@UserId") Is Nothing
                Else
                    bAddUserParameter = True
                End If


                If bAddUserParameter Then
                    cmd.Parameters.Add(UserParameter(UserId))
                End If

                cmd.Parameters.Add(ReturnParameter)

                If oReturn Is Nothing Then
                    cmd.ExecuteNonQuery()
                Else
                    oReturn.Load(cmd.ExecuteReader)
                End If

                lRet = DirectCast(cmd.Parameters("@Return_Value").Value, Integer)

                cmd.Parameters.Clear()
                If Not Params Is Nothing Then
                    For Each param As SqlParameter In Params
                        param.ParameterName = Replace(param.ParameterName, "@", "")
                    Next
                End If
            End Using
        End Using

        Return lRet

    End Function

    Private Function FindParameter(ByVal Params As List(Of IDataParameter), ByVal sParameterName As String) As SqlParameter
        For Each Param As IDataParameter In Params
            If Param.ParameterName = sParameterName Then
                Return Param
            End If
        Next
        Return Nothing
    End Function

    ''' default connection dal config dell'assembly principale
    Private Function GetCnn() As SqlConnection

        Dim cnnString As String = ConnectionString
        If String.IsNullOrEmpty(cnnString) Then
            Throw New FormatException("Connection string missing. Verificare il Config per ConnectionString")
        End If

        'Return ConfigurationManager.ConnectionStrings.Item( _
        'ConfigurationManager.AppSettings.Get("ConnectionString")).ConnectionString

        Return New SqlConnection(cnnString)

    End Function

    Private Function UserParameter(ByVal UserId As Integer) As SqlParameter

        Dim oParam = New SqlParameter()

        With oParam
            .DbType = SqlDbType.Int
            .ParameterName = "@UserId"
            .Value = UserId
            .Direction = ParameterDirection.Input
        End With

        Return oParam

    End Function

    Private Function ReturnParameter() As SqlParameter

        Dim oParam = New SqlParameter()

        With oParam
            .DbType = SqlDbType.Int
            .ParameterName = "@Return_Value"
            .Direction = ParameterDirection.ReturnValue
        End With

        Return oParam

    End Function


    Public Function DataAdapter() As System.Data.IDataAdapter Implements IDbProvider.DataAdapter
        Throw New NotImplementedException
        Return Nothing
    End Function

    Private Sub AddParamsToCmd(ByVal Params As List(Of IDataParameter), ByVal cmd As SqlCommand)

        For Each param As SqlParameter In Params
            If Not Left(param.ParameterName, 1) = "@" Then
                param.ParameterName = "@" + param.ParameterName

            End If
            cmd.Parameters.Add(param)
        Next

    End Sub

End Class



